import requests
import numpy as np
import seaborn as sns
import json
import matplotlib.pyplot as plt
import pandas as pd
import holoviews as hv
from holoviews import opts, dim
from bokeh.sampledata.les_mis import data
hv.extension('bokeh')
hv.output(size=200)
I formulate the query to the GraphQL API implemented by the subgraph TetherUSDT: in detail, I want to retrieve the following features with regard to issues, transfers and approvals.
query = """query {
issues(orderBy: amount, orderDirection: asc) {
id
amount
}
transfers (first:1000) {
id
from
to
value
}
approvals {
id
owner
spender
value
}
}"""
I use a temporary query url instead of the real query URL with the generated API key (https://gateway.testnet.thegraph.com/api/e0e91a841b861b1b22da614b4d6ef4b7/subgraphs/id/EW29ZpxgJRNUzjW5qs1KRyijWh2t171WmgNJ8sNmN7P5), since the latter method requires the adding of some GRT to my billing balance.
url = "https://api.studio.thegraph.com/query/16935/tetherusdt/v0.0.3"
r = requests.post(url, json={'query': query})
print(r.status_code)
#print(r.text)
200
json_data = json.loads(r.text)
df_issues = pd.DataFrame(json_data["data"]["issues"])
df_issues['amount'] = pd.to_numeric(df_issues['amount'])
The first 5 records of issues DataFrame.
df_issues.head()
| id | amount | |
|---|---|---|
| 0 | 0x2ec30181b26f842558280b682a5a58e15cae6498b4c1... | 8005642000 |
| 1 | 0x8cfc4f5f4729423f59dd1d263ead2f824b3f133b02b9... | 10000000000 |
| 2 | 0x27b0df3879a34fff8abd827eb0a05892041af7763a88... | 10000000000000 |
| 3 | 0x31f01e3f69d763c70e9965c370475f454338effdcca4... | 15000000000000 |
| 4 | 0x79595df1a5d8b96c017cd78bbd844fb68c94c19edb55... | 15000000000000 |
values = list(df_issues['amount'].value_counts().sort_index().keys())
frequencies = df_issues['amount'].value_counts().sort_index()
plt.figure(figsize=(35,10))
ax = sns.barplot(x=values, y=frequencies)
ax.set_xticklabels(ax.get_xticklabels(),rotation = 30)
xlabels = [j//1000000000 for j in values]
ax.set_xticklabels(xlabels)
plt.title("Bar plot of the frequencies of issues amounts (in billion)")
sns.set(font_scale = 2)
plt.show()
df_approvals = pd.DataFrame(json_data["data"]["approvals"])
df_approvals.head()
| id | owner | spender | value | |
|---|---|---|---|---|
| 0 | 0x00006c3fc1b710ac8519fc19b568a641ef3ce1f51aa0... | 0xf9b42f4c01c51847d16de067a62dc823487dc18f | 0x0eee3e3828a45f7601d5f54bf49bb01d1a9df5ea | 1157920892373161954235709850086879078532699846... |
| 1 | 0x0000b23d81498f6665dfed904fd29eaea290874ff852... | 0xf0fce8c7029175f04ec789fe30a77b7731832bfc | 0x818e6fecd516ecc3849daf6845e3ec868087b755 | 5789604461865809771178549250434395392663499233... |
| 2 | 0x0000dc41a87775f20fb724cea53f1d4263df2c1834cb... | 0x806ff9f29d137c6cdbf3f16f30396123aa567a20 | 0x2faf487a4414fe77e2327f0bf4ae2a264a776ad2 | 1157920892373161954235709850086879078532699846... |
| 3 | 0x0001011e6128b3dfa3b9c47465250d9c17b1e3f1cd95... | 0x6a2abcce75788b2565bfdb5065168f2c5912c19b | 0xf650c3d88d12db855b8bf7d11be6c55a4e07dcc9 | 1157920892373161954235709850086879078532699846... |
| 4 | 0x00016199dfbf0b0950f1fd95e0a2ed082776b78c5243... | 0x4166f82410b139a2f7b1a28b07b823b6f2ce92db | 0x41f8d14c9475444f30a80431c68cf24dc9a8369a | 1157920892373161954235709850086879078532699846... |
The value feature assumes incredibly high number.
df_approvals['value'][0]
'115792089237316195423570985008687907853269984665640564039457584007913129639935'
In order to plot these values, I try to manage their magnitude through standardization (min-max scaling), however I discover that the best approach is approximate each amount with the number of its digits.
values_length = [len(i) for i in df_approvals['value']]
plt.figure(figsize=(30,6))
sns.histplot(values_length, discrete=True)
plt.title("Frequency distribution of values' length")
sns.set(font_scale = 0.5)
plt.show()
df_approvals['value'] = values_length
graph = hv.Sankey(df_approvals.iloc[:, 1:])
graph.opts(
opts.Sankey(label_position='left', width=600, height=1100, cmap='Set1',
edge_color=dim('spender').str(), node_color=dim('spender').str()))
df_transfers = pd.DataFrame(json_data["data"]["transfers"])
df_transfers['value'] = pd.to_numeric(df_transfers['value'])
The first 5 records of transfers DataFrame.
df_transfers.head()
| id | from | to | value | |
|---|---|---|---|---|
| 0 | 0x0000000fc33284d2faf6050890764158f09a838db415... | 0x5694d5d33c810d7929a385ff936217f07ecd046f | 0x358f78edabf6556d3d84b3758110a77b30b2868b | 1800000000 |
| 1 | 0x0000004344abcb2643843225b031a4bdb2c6b6a69ba0... | 0xfe7e6564a8b820e2e7363c5460edec4cc7743faa | 0xa5407eae9ba41422680e2e00537571bcc53efbfd | 0 |
| 2 | 0x000000ae8958e6020b8d6bd7c57ec675f1200188f56e... | 0xcd3d365c3c8f158a5ad4fd4a5659b8a1f9b0d03d | 0x1062a747393198f70f71ec65a582423dba7e5ab3 | 122541997 |
| 3 | 0x00000112ed5c1d8741bb77c2b0920758ea29bc1c567a... | 0xcf3618d4680817af786a1d93465a19ab4225e69e | 0xd331227a7fe6682a93e8fa07700779f11996dd3a | 527000000 |
| 4 | 0x000001583813f32c6badcf580cabf37a20ba717e48ac... | 0xd70177a0a6b5b1cb0ad586d419b693cfa631b097 | 0x818157b54809eec4f36b56e752cd695dba11d50d | 987000000 |
df_transfers['from'].value_counts()[:15]
0x1062a747393198f70f71ec65a582423dba7e5ab3 29 0xfdb16996831753d5331ff813c29a93c76834a0ad 28 0xadb2b42f6bd96f5c65920b9ac88619dce4166f94 19 0xab5c66752a9e8167967685f1450532fb96d5d24f 18 0xeee28d484628d41a82d01e21d12e2e78d69920da 17 0x46705dfff24256421a05d056c29e81bdc09723b8 16 0x0a98fb70939162725ae66e626fe4b52cff62c2e5 15 0x6748f50f686bfbca6fe8ad62b22228b87f31ff2b 14 0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be 12 0x5041ed759dd4afc3a72b8192c143f72f4724081a 11 0xe93381fb4c4f14bda253907b18fad305d799241a 11 0xd551234ae421e3bcba99a0da6d736074f22192ff 9 0xc6bd3edd07e294cb66b8318356d688b3516ea950 8 0x0d0707963952f2fba59dd06f2b425ace40b492fe 7 0x035cb93d40a60d9adcd0a38954e802720996b481 6 Name: from, dtype: int64
df_transfers['to'].value_counts()[:15]
0x6748f50f686bfbca6fe8ad62b22228b87f31ff2b 12 0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be 12 0xab5c66752a9e8167967685f1450532fb96d5d24f 9 0xeee28d484628d41a82d01e21d12e2e78d69920da 9 0x46705dfff24256421a05d056c29e81bdc09723b8 8 0x1062a747393198f70f71ec65a582423dba7e5ab3 8 0xfdb16996831753d5331ff813c29a93c76834a0ad 7 0xadb2b42f6bd96f5c65920b9ac88619dce4166f94 7 0xe93381fb4c4f14bda253907b18fad305d799241a 7 0x5041ed759dd4afc3a72b8192c143f72f4724081a 7 0x6cc5f688a315f3dc28a7781717a9a798a59fda7b 7 0x0a98fb70939162725ae66e626fe4b52cff62c2e5 7 0x75e89d5979e4f6fba9f97c104c2f0afb3f1dcb88 6 0x4c3dc6d5d3fc47060b5644d057aeb12d31fa79ee 6 0x0d4a11d5eeaac28ec3f61d100daf4d40471f1852 4 Name: to, dtype: int64
chord = hv.Chord(df_transfers.iloc[:, 1:])
chord.opts(
opts.Chord(cmap='Set1', edge_cmap='Set1', edge_color='from', labels='from', node_color='from', width=600, height=600))
chord = hv.Chord(df_transfers.iloc[:100, 1:])
chord.opts(
opts.Chord(cmap='Set1', edge_cmap='Set1', edge_color='from', labels='from', node_color='from', width=600, height=600))
The GraphQL query language presents high flexibility regarding query constructs: for instance, I can query a subset of the available features of transfers filtering by a specific source address.
query_filtered = """query {
transfers(
where: {
from: "0xfdb16996831753d5331ff813c29a93c76834a0ad"},
orderBy: value,
orderDirection: desc
) {
from
to
value
}
}"""
r = requests.post(url, json={'query': query_filtered})
print(r.status_code)
#print(r.text)
json_data = json.loads(r.text)
df_transfers_filtered = pd.DataFrame(json_data["data"]["transfers"])
200
df_transfers_filtered
| from | to | value | |
|---|---|---|---|
| 0 | 0xfdb16996831753d5331ff813c29a93c76834a0ad | 0xab5c66752a9e8167967685f1450532fb96d5d24f | 32619254448700 |
| 1 | 0xfdb16996831753d5331ff813c29a93c76834a0ad | 0x418b9d7fd9b3ded3c23668a60865952accd0096e | 10000000000000 |
| 2 | 0xfdb16996831753d5331ff813c29a93c76834a0ad | 0xe71d51b03015d0ba6158a4dca6e56256c5020a1d | 4000000000000 |
| 3 | 0xfdb16996831753d5331ff813c29a93c76834a0ad | 0xe71d51b03015d0ba6158a4dca6e56256c5020a1d | 4000000000000 |
| 4 | 0xfdb16996831753d5331ff813c29a93c76834a0ad | 0x069d76ffd7906e4ab4307c1287282a8a9247e938 | 2899999000000 |
| ... | ... | ... | ... |
| 95 | 0xfdb16996831753d5331ff813c29a93c76834a0ad | 0x79115db24062f17d53f52557845d1d61f12c8272 | 1000000000000 |
| 96 | 0xfdb16996831753d5331ff813c29a93c76834a0ad | 0x80cfe8be400ded3a2bc23bbf760f52546a976029 | 1000000000000 |
| 97 | 0xfdb16996831753d5331ff813c29a93c76834a0ad | 0xc148955b92475df692f0bfb01a494248d1a6b75b | 1000000000000 |
| 98 | 0xfdb16996831753d5331ff813c29a93c76834a0ad | 0x44f3092a2dd628df99988db65fa20e19586797f5 | 1000000000000 |
| 99 | 0xfdb16996831753d5331ff813c29a93c76834a0ad | 0x29899ef9d9430c0917644d407ef8fe72a3ee4c62 | 1000000000000 |
100 rows × 3 columns